package Actions;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.struts.action.*;
import java.sql.*;
import java.util.*; 
import support.*;
import java.text.DecimalFormat;


public class appsAction extends Action 
{
	public ActionForward execute(ActionMapping mapping, ActionForm form,
			HttpServletRequest request, HttpServletResponse response){
		
		forms.adminAnaform ar = (forms.adminAnaform) form;
		//get which page is it from
		String WhichPage = (String)(request.getSession().getAttribute("WhichPage"));
		String url = "jdbc:postgresql://localhost:5432/Applications";
		Connection conn = null;
		Statement stmt = null;
		Statement stmt2 = null;
		Statement stmt3 = null;
		Statement stmt4 = null;
		Statement stmt5 = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		ResultSet rs4 = null;	
		ResultSet rs5 = null;	
		
		ar.resetNames();
		try {
			// Registering Postgresql JDBC driver
			Class.forName("org.postgresql.Driver");
			// Open a connection to the database	
			conn = DriverManager.getConnection(url,"postgres","postgres");
			// Create the statement
	        stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
	                    				ResultSet.CONCUR_UPDATABLE);
	        stmt2 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);			
	        stmt3 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);	        
	        stmt4 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);
	        stmt5 = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
    				ResultSet.CONCUR_UPDATABLE);	        
	        //access directly
			if(WhichPage.equals("Rev")){
				ar.resetNames();
				String whichtype = (String) request.getSession().getAttribute("whichtype");
				String whichreviewer = (String) request.getSession().getAttribute("whichreviewer");
				System.out.print("whichtype:"+whichtype+" whichR:"+whichreviewer);
				
				if(whichtype.equals("Grade")){
					//find out reviewer ID
					rs = stmt.executeQuery("SELECT rev_id FROM Reviewers WHERE username ='"
											+whichreviewer+"'");
					int rev_id = -1;
					while(rs.next()){
						rev_id = rs.getInt("rev_id");
					}
					rs2 = stmt2.executeQuery("SELECT distinct * FROM Application WHERE "
							+"Grader1 = '"+rev_id+"' OR "
							+"Grader2 = '"+rev_id+"' OR "
							+"Grader3 = '"+rev_id+"'");
					
					while(rs2.next()){
						rs4 = stmt4.executeQuery("SELECT distinct NameID FROM MainTable WHERE AppID = '"
							+ rs2.getInt("app_id") +"'");
						rs = stmt.executeQuery("SELECT * FROM NAME WHERE NameID = "
								+"(SELECT distinct NameID FROM MainTable WHERE AppID = '"
								+rs2.getInt("app_id")+"')");
						
						while(rs4.next()){
							rs.next();
							rs3 = stmt3.executeQuery("SELECT user_name FROM users WHERE APP ="
													+rs4.getInt("NameID")+"");
							String username = "";
							while(rs3.next()){
								username = rs3.getString("user_name");
							}
							double avg = (rs2.getInt("Grade1") + rs2.getInt("Grade2") + rs2.getInt("Grade3"))/3;
							DecimalFormat df = new DecimalFormat("0.00");
							System.out.println(df.format(avg));
							ar.updateNames(new forms.nameform(rs.getString("First"),rs.getString("Middle"),rs.getString("Last"),
												df.format(avg),rs2.getString("status"),username));
						}
					}

				}
				//show not graded
				else{
					rs = stmt.executeQuery("SELECT rev_id FROM Reviewers WHERE username ='"
							+whichreviewer+"'");
					int rev_id = -1;
					//get reviewer id
					while(rs.next()){
						rev_id = rs.getInt("rev_id");
					}
					rs4 = stmt4.executeQuery("SELECT distinct app_id FROM Application WHERE "
							+"rev_id = '"+rev_id+"'");
					while(rs4.next()){
					
							rs = stmt.executeQuery("SELECT * FROM NAME WHERE NameID = "
													+"(SELECT distinct NameID FROM MainTable WHERE AppID = "
													+rs4.getInt("app_id")+")");
							rs2 = stmt2.executeQuery("SELECT distinct * FROM Application WHERE "
									+"rev_id = '"+rev_id+"' ");
							//save infos
							while(rs.next()){
								System.out.println("here1");
								rs2.next();
									rs3 = stmt3.executeQuery("SELECT user_name FROM users WHERE APP ="
											+"(SELECT distinct NameID FROM MainTable WHERE AppID = '"
											+ rs2.getInt("app_id") +"')");
									String username = "";
									while(rs3.next()){
										username = rs3.getString("user_name");
									}
									System.out.println("here2");
									double avg = (rs2.getInt("Grade1") + rs2.getInt("Grade2") + rs2.getInt("Grade3"))/3;
									DecimalFormat df = new DecimalFormat("0.00");
									ar.updateNames(new forms.nameform(rs.getString("First"),rs.getString("Middle"),rs.getString("Last"),
														df.format(avg),rs2.getString("status"),username));
								
							}
					}
				}

			}
			//from SpecAna page
			else if( WhichPage.equals("Spec") )
			{
				ar.resetNames();
				//get which Spec
				String Spec = (String) request.getSession().getAttribute("WhichSpec");
				
				//rs = the application with that Spec
				rs = stmt.executeQuery("SELECT distinct NAMEID, AppID FROM MainTable WHERE SpecID="
										+"(SELECT ID FROM Specializations WHERE Specialization ='"+ Spec +"')");
										
				int last =0;
				while(rs.next()){
					//make sure not to show the name twice with same ID
					if( rs.getInt("AppID")!=last )
					{
						last = rs.getInt("AppID");
						System.out.println("fasfsafdafdsfsafda:"+last );
						rs3 = stmt3.executeQuery("SELECT First, Middle, Last FROM NAME WHERE NAMEID ='"+rs.getInt("NAMEID")+"'");
						rs2 = stmt2.executeQuery("SELECT distinct * FROM Application WHERE "
								+"app_id = '"+last+"' ");
						while(rs2.next()){
							rs3.next();
							rs4 = stmt4.executeQuery("SELECT user_name FROM users WHERE APP ="
									+"(SELECT distinct NameID FROM MainTable WHERE AppID = '"
									+ rs2.getInt("app_id") +"')");
							String username = "";
							while(rs4.next()){
								username = rs4.getString("user_name");
							}
							double avg = (rs2.getInt("Grade1") + rs2.getInt("Grade2") + rs2.getInt("Grade3"))/3;
							DecimalFormat df = new DecimalFormat("0.00");
							ar.updateNames(new forms.nameform(rs3.getString("First"),rs3.getString("Middle"),rs3.getString("Last"),
												df.format(avg),rs2.getString("status"),username));
						}
					}
				}	
			}
			else if( WhichPage.equals("Discp") )
			{			
				String Major = (String) request.getSession().getAttribute("WhichMajor");
				
				//rs = person with that major in degree
				rs = stmt.executeQuery("SELECT DegID FROM DEGREE WHERE Major = "
						+"(SELECT ID FROM Disciplines WHERE Discipline ='"+ Major +"')");
				int last = 0;
				//System.out.println("fadfasdfsafsfdsaf");
				while( rs.next() ) 
				{
					rs4 = stmt4.executeQuery("SELECT NAMEID, AppID FROM MainTable WHERE DegID = '"
											+rs.getInt("DegID")+"'");
					rs4.next();
					//make sure not the show the person twice with same APPID
					if( rs4.getInt("AppID")!=last )
					{
						//ar.resetNames();
						
						last = rs4.getInt("AppID");
						rs3 = stmt3.executeQuery("SELECT First, Middle, Last FROM NAME WHERE NAMEID ='"+rs4.getInt("NAMEID")+"'");
						rs2 = stmt2.executeQuery("SELECT distinct * FROM Application WHERE "
								+"app_id = '"+last+"' ");
						while(rs2.next()){
							rs3.next();
							
							rs5 = stmt5.executeQuery("SELECT user_name FROM users WHERE APP ="
									+"(SELECT distinct NameID FROM MainTable WHERE AppID = '"
									+ rs2.getInt("app_id") +"')");
							String username = "";
							while(rs5.next()){
								username = rs5.getString("user_name");
							}
							
							double avg = (rs2.getInt("Grade1") + rs2.getInt("Grade2") + rs2.getInt("Grade3"))/3;
							DecimalFormat df = new DecimalFormat("0.00");
							ar.updateNames(new forms.nameform(rs3.getString("First"),rs3.getString("Middle"),rs3.getString("Last"),
												df.format(avg),rs2.getString("status"),username));
						}
					}
				}
			}

			if(rs != null)
			rs.close();
			if(rs2 != null)
			rs2.close();
			if(rs3 != null)
				rs3.close();
			if(rs4 != null)
				rs4.close();
			stmt.close();
		    conn.close();		
		} catch (SQLException e) 
		  { 
			System.out.println(e);
		  } catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return mapping.findForward("appSuccess");
	}
}
